﻿using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DA
{
    public class clsConnect
    {
        #region[Define]
        private static string sql = ConfigurationManager.ConnectionStrings["Connect"].ConnectionString;
        public static SqlConnection cn;
        SqlCommand cmd;
        #endregion

        #region[Connection]
        public clsConnect()
        {
            if (cn == null)
            {
                cn = new SqlConnection(sql);
            }
        }
        public static SqlConnection GetConnection()
        {
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
                return cn;
            }
            else
            {
                return cn;
            }
        }
        #endregion

        #region[Get-Data]
        //--------------SQLDataReader-------------------
        public DataTable GetData(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, GetConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public DataTable GetData_ID_Int(string sql, string paramName, int id)
        {
            SqlCommand cmd = new SqlCommand(sql, GetConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(paramName, SqlDbType.Int).Value = id.ToString();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public DataTable GetData_ID_Int_Int(string sql, string paramName_1,string paramName_2, int id_1,int id_2)
        {
            SqlCommand cmd = new SqlCommand(sql, GetConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(paramName_1, SqlDbType.Int).Value = id_1.ToString();
            cmd.Parameters.Add(paramName_2, SqlDbType.Int).Value = id_2.ToString();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public DataTable GetData_ID_String(string sql,string paramName, string id)
        {
            SqlCommand cmd = new SqlCommand(sql, GetConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(paramName, SqlDbType.NVarChar).Value = id.ToString();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        #endregion

        #region[Del]
        public void Delete(string sql, string paramName, int id)
        {
            //cn.Open();
            SqlCommand cmd = new SqlCommand(sql, GetConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(paramName, SqlDbType.NVarChar).Value = id.ToString();
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public void Delete_All_DB(string sql)
        {
            //cn.Open();
            SqlCommand cmd = new SqlCommand(sql, GetConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
            //cn.Close();
        }
        #endregion

        #region[Create-SqlCommand]
        public void CreateNewSqlCommand()
        {
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = GetConnection();
        }
        public void AddParameter(string paramName, object value)
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = paramName;
            param.Value = value;
            cmd.Parameters.Add(param);
        }
        #endregion

        #region[ExecuteSqlCommand]
        public int ExecuteNonQuery(string sProcName)
        {
            cmd.CommandText = sProcName;
            GetConnection();
            return cmd.ExecuteNonQuery();
        }
        #endregion

        #region[Search]
        public DataTable Search(string sql, string paramName,string keywords)
        {
            SqlCommand cmd = new SqlCommand(sql, GetConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(paramName, SqlDbType.NVarChar).Value = keywords.ToString();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        #endregion

        #region[Fillter-Date]
        public DataTable FillterDay(string sql, string DateBegin, string EndBegin)
        {
            SqlCommand cmd = new SqlCommand(sql, GetConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@DateBegin", SqlDbType.NVarChar).Value = DateBegin.ToString();
            cmd.Parameters.Add("@EndBegin", SqlDbType.NVarChar).Value = EndBegin.ToString();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            //cn.Close();
            return dt;
        }
        #endregion

        #region[Fillter-Status]
        public DataTable FillterTrangThai(string sql, string TrangThai)
        {
            SqlCommand cmd = new SqlCommand(sql, GetConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TrangThai", SqlDbType.NVarChar).Value = TrangThai.ToString();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            //cn.Close();
            return dt;
        }
        #endregion
    }
}
